Walmart Sales Forecast¶
This notebook explores the sales data of 45 Wal-Mart locations. Exploritory data analysis was perfomed at the store and deparment level and the finding were used to forecast the sales.
Functionality SettingIn [1]:
import warnings
warnings.filterwarnings("ignore")
DependenciesIn [2]:
import matplotlib.pyplot as plt
#%matplotlib inline
import plotly.graph_objects as go
import plotly.express as px
from plotly.subplots import make_subplots
import plotly.io as pio
pio.renderers.default='notebook'
import seaborn as sns
import pandas as pd
import numpy as np
import datetime
1. Data Imports and Manipulation¶
The sales and store data was collected from Walmart Recruiting's Kaggle Competition. This dataset only includes the influence of 4 holidays. Entering this project with the assumption that consumer spending is heavily influenced by holidays so a second source was utilized to collect all relevant consumer events and commonly observed U.S. holidays. The datasets were then consolidated in a separate script (due to its extended runtime) and saved in sales_updated.csv.
In [3]:
csv = pd.read_csv("data/holidays.csv")
holidays = pd.DataFrame(csv)
holidays.head()
Out[3]:
In [4]:
holidays.Date = pd.to_datetime(holidays.Date)
In [5]:
#walmart's
csv = pd.read_csv("data/sales.csv")
sales = pd.DataFrame(csv)
sales.tail()
Out[5]:
In [6]:
sales.Date = pd.to_datetime(sales.Date)
In [7]:
#all
csv = pd.read_csv("data/sales_updated.csv")
sales_all = pd.DataFrame(csv)
sales_all.tail()
Out[7]:
In [8]:
sales_all.Date = pd.to_datetime(sales.Date)
In [9]:
sales.info()
sales.describe()
Out[9]:
In [10]:
csv = pd.read_csv("data/stores.csv")
stores = pd.DataFrame(csv)
stores.tail()
Out[10]:
In [11]:
stores.info()
stores.describe()
Out[11]:
2. EDA¶
In [12]:
#setting color pallete for charts
pio.templates.default = "plotly_white"
palette = flatui = ["#9b59b6", "#3498db", "#95a5a6", "#e74c3c", "#34495e", "#2ecc71"]
sns.set_palette(palette)
plt.style.use('seaborn-darkgrid')
Record Counts¶
Review missing values
In [13]:
sales.nunique()
Out[13]:
In [14]:
45*81*143 == len(sales)
Out[14]:
In [15]:
#Departments with missing weekly sales
sales.groupby('Dept').nunique().query("Date<143")
Out[15]:
In [16]:
#Stores with missing weekly sales
sales.groupby('Store').nunique().query("Date<143")
Out[16]:
In [17]:
#Number of unique depts per store
sales.groupby('Store').nunique().sort_values(['Dept']).plot()
Out[17]:
Sales Analysis¶
High-level analysis of sales and holiday's potential influence on seasonality
In [18]:
byDate = sales.groupby('Date',sort=False).sum()
byDate.IsHoliday = byDate.IsHoliday.astype('bool').astype('int')
byDate.index.freq = 'W-FRI'
byDate.head()
Out[18]:
In [19]:
holidaysdf = byDate.query("IsHoliday==1")
In [20]:
line = go.Figure(data=go.Scatter(x=byDate.index,y=byDate.Weekly_Sales, name='Weekly_Sales'))
line.update_layout(title={
'text': "Total Weekly Sales",
'y':0.9,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'}
)
line.update_xaxes(
rangeslider_visible=True,
rangeselector=dict(
buttons=list([
dict(count=1, label="1m", step="month", stepmode="backward"),
dict(count=6, label="6m", step="month", stepmode="backward"),
dict(count=1, label="1y", step="year", stepmode="backward"),
dict(step="all")
])
)
)
line.show()
In [21]:
ymin=byDate.Weekly_Sales.min()
ymax=byDate.Weekly_Sales.max()
lin = go.Figure(data=go.Scatter(x=byDate.index,y=byDate.Weekly_Sales, name='Weekly_Sales'))
final_wk = max(byDate.index)
for date in byDate.query("IsHoliday==1").index:
lin.add_shape(type="line",
x0=date,
y0=ymin,
x1=date,
y1=ymax,
opacity=0.3,
line=dict(color="RoyalBlue"))
lin.update_layout(title={
'text': "Total Weekly Sales with Walmart's Holiday Markers",
'y':0.9,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'})
lin.show()
In [22]:
lin = go.Figure(data=go.Scatter(x=byDate.index,y=byDate.Weekly_Sales, name='Weekly_Sales'))
final_wk = max(byDate.index)
for index, row in holidays[holidays.Date <= final_wk].iterrows():
lin.add_shape(type="line",
x0=row['Date'],
y0=ymin,
x1=row['Date'],
y1=ymax,
opacity=0.3,
line=dict(color="RoyalBlue"))
lin.update_layout(title={
'text': "Total Weekly Sales with All Holiday Markers",
'y':0.9,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'})
lin.show()
Day-of and trailing/leading
Sales by Store¶
How does the stores' size/type influences sales?
In [23]:
byStore = sales.reset_index().groupby('Store', as_index=False).sum()
byStore = pd.merge(byStore, stores, on='Store', how='left')
byStore.head()
Out[23]:
In [24]:
byStore.nunique()
Out[24]:
In [25]:
#Formatting df for visuals
byStore = byStore[["Store", "Weekly_Sales", "Type", "Size"]]
byStore.head()
Out[25]:
In [26]:
byStore_Bar = go.Figure(data=go.Bar(x=byStore.Store,
y=byStore.Weekly_Sales,
name='Weekly_Sales')
)
byStore_Bar.update_layout(title={
'text': "Total Weekly Sales with All Holiday Markers",
'y':0.9,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'})
byStore_Bar.show()
SOME STORE THAT OUTPERFORM OTHERS...POTENTIALLY INFLUENCED BY Type SIZE LOCATION ETC...
In [27]:
fig = px.box(byStore, x="Type", y="Weekly_Sales", color="Type")
fig.show()
In [28]:
fig = px.box(byStore, x="Type", y="Size", color="Type")
fig.show()
In [29]:
size_Dist = px.histogram(byStore, x="Size", color='Type')
size_Dist.show()
In [30]:
fig = px.scatter(byStore, x="Size",y='Weekly_Sales',color='Type')
fig.show()
In [31]:
byStore['SalesPerSF'] = byStore.Weekly_Sales/byStore.Size
fig = px.scatter(byStore, x="Size",y='SalesPerSF',color='Type')
fig.show()
types seem to be based soley on size....smaller store making best use of space...larger store appear to be average ....medium have lowest performance
...type influenced by size...bc previous does not provided the desired detail...dig deeper into size..cluster to further analyze the sales behavior across stores SIZES...
Cluster Stores in hope to find stronger relationships...use ml to cluter store based on size
In [32]:
byStorex = byStore[['SalesPerSF','Weekly_Sales', 'Size']]
byStorex.head()
Out[32]:
In [33]:
from sklearn.cluster import KMeans
kmeans = KMeans(n_clusters=6)
kmeans.fit(byStorex)
byStorex['Cluster'] = kmeans.labels_
byStorex.head()
Out[33]:
NOW THAT THE STORE ARE CLUSTERED...MORE MEANINGFUL inferences...size's influence on sales
In [34]:
fig = px.scatter(byStorex, x="Size",y='SalesPerSF',color='Cluster', size='Weekly_Sales')
fig.show()
In [35]:
fig = px.box(byStorex, x="Cluster", y="SalesPerSF", color="Cluster")
fig.show()
FINAL STORE SALES THOUGHTS...
Sales by Dept¶
Which departments are influenced by seasonality?
In [36]:
#number of unique departments
sales['Dept'].nunique()
Out[36]:
In [37]:
byDept = sales.groupby(['Dept']).sum()
byDept.head()
Out[37]:
In [38]:
byDept_Bar = px.bar(byDept, y='Weekly_Sales')
byDept_Bar.show()
as expected prices vary very heavily among depts...negative sales (loss) observed...filter store with negative sales
In [39]:
byDept.query('Weekly_Sales<=0')
Out[39]:
DICKEY-FULLER TEST¶
determine stationarity...lags!!
In [40]:
from statsmodels.tsa.stattools import adfuller
In [41]:
#salesx = pd.read_csv("data/sales.csv")
#salesx.head()
In [42]:
#median number of records for depts with NO missing week
sales.groupby('Dept').nunique().sort_values('Date').query('Date==143')['Weekly_Sales'].median()
Out[42]:
In [43]:
sales.Date.nunique()
Out[43]:
In [44]:
#Depts with missing weeks
dept_w_missing_wks = sales.groupby('Dept').nunique().sort_values('Date').query('Date<143')
missing_wks = dept_w_missing_wks.index
In [45]:
for dept in missing_wks:
data = sales.query(f"Dept=={dept}").groupby('Date').sum()
fig = px.line(data, y="Weekly_Sales")
fig.show()
In [46]:
stat_list = []
stat_bool_list = []
dept_list = []
depts = sales.Dept.unique()
for dept in depts:
if dept in missing_wks:
continue
byDeptx = sales.query(f"Dept=={dept}").groupby('Date').sum()
dftest = adfuller(byDeptx['Weekly_Sales'], maxlag=55)
p_value = dftest[1]
lags = dftest[2]
if p_value <= 0.05:
stationarity = "Stationary"
station_bool = 1
else:
stationarity = "Non-Stationary"
station_bool = 0
dept_list.append(dept)
stat_list.append(stationarity)
stat_bool_list.append(station_bool)
stationarities = pd.DataFrame({'Dept': dept_list,
'Stationary': stat_list,
'Stationary_Bool': stat_bool_list})
stationarities.head()
Out[46]:
STATIONARY VS NON-STATIONARY...COUNT, %OF DEPT, %OF SALES DEPEND ON SEASONALITY/TRENDS??
In [47]:
byStat = pd.merge(sales, stationarities, on='Dept', how='left')
byStat = byStat.groupby(['Stationary','Date'], as_index=False).sum()
byStat.head()
Out[47]:
In [48]:
byStat_line = px.line(byStat,x='Date', y='Weekly_Sales', color='Stationary')
byStat_line.show()
In [49]:
fig = px.pie(byStat, values='Weekly_Sales', names='Stationary')
fig.show()
3. Forecasting¶
describe methodology..stationarity/DFT results determine model type(ARIMA or SARIMAx)...Eget felis eget nunc lobortis mattis. Vulputate sapien nec sagittis aliquam malesuada bibendum. Adipiscing tristique risus nec feugiat in fermentum posuere urna. Sapien pellentesque habitant morbi tristique senectus et netus. Faucibus scelerisque eleifend donec pretium vulputate sapien nec. Eget egestas purus viverra
Seasonal Decomposition¶
In [50]:
from statsmodels.tsa.seasonal import seasonal_decompose
result = seasonal_decompose(byDate['Weekly_Sales'], model='add')
result.plot();
SARIMA¶
In [51]:
from statsmodels.tsa.statespace.sarimax import SARIMAX
from statsmodels.graphics.tsaplots import plot_acf,plot_pacf # for determining (p,q) orders
from pmdarima import auto_arima
In [52]:
models = byDate[['Weekly_Sales', 'IsHoliday']]
models.IsHoliday = byDate.IsHoliday.astype('bool').astype('int')
models.head()
Out[52]:
In [53]:
models.index.freq = 'W-FRI'
models.index
Out[53]:
In [54]:
#confirm stationarity
adfuller(models['Weekly_Sales'], maxlag=55)
Out[54]:
In [55]:
#Run pmdarima.auto_arima to obtain recommended orders
auto_arima(models['Weekly_Sales'],seasonal=True,m=52).summary()
Out[55]:
In [56]:
#Split the data into train/test setsdates = models.index
length = len(models)
train_len = int(length*0.70)
train = models.iloc[:train_len]
test = models.iloc[train_len:]
In [57]:
#Fit a SARIMA(1,0,0)(2,0,0,7) Model
model1 = SARIMAX(train['Weekly_Sales'],order=(2,0,2),seasonal_order=(1,0,0,52),enforce_invertibility=False)
results = model1.fit()
results.summary()
Out[57]:
In [58]:
# Obtain predicted values
start=len(train)
end=len(train)+len(test)-1
predictions1 = results.predict(start=start, end=end, dynamic=False)
models['NoHolidays'] = predictions1
models.dropna(inplace=True)
models.head()
Out[58]:
In [59]:
models_lines = go.Figure()
models_lines.add_trace(
go.Scatter(
x=models.index,
y=models.Weekly_Sales,
name = "Weekly Sales - Actual"
))
models_lines.add_trace(
go.Scatter(
x=models.index,
y=models.NoHolidays,
name = "Model 1 - No Holidays"
))
models_lines.show()
In [60]:
from statsmodels.tools.eval_measures import mse,rmse
mse1 = mse(test['Weekly_Sales'], predictions1)
rmse1 = rmse(test['Weekly_Sales'], predictions1)
print(f"MSE: {mse1}\nRMSE: {rmse1}")
SARIMAX - WalMart Holidays¶
add the exog variable
In [61]:
model2 = SARIMAX(train['Weekly_Sales'],exog=train['IsHoliday'],order=(2,0,2),seasonal_order=(1,0,0,52),enforce_invertibility=False)
results = model2.fit()
results.summary()
Out[61]:
In [62]:
# Obtain predicted values
start=len(train)
end=len(train)+len(test)-1
exog_forecast = test[['IsHoliday']] # requires two brackets to yield a shape of (35,1)
predictions2 = results.predict(start=start, end=end, exog=exog_forecast)
models['WalmartHolidays'] = predictions2
models.head()
Out[62]:
In [63]:
models_lines.add_trace(
go.Scatter(
x=models.index,
y=models.WalmartHolidays,
name = "Model 2 - with Walmart Holidays"
))
models_lines.show()
In [64]:
testmin = test.index.min()
testmax = test.index.max()
trainmin = train.index.min()
trainmax = train.index.max()
print(f"Train Set: {trainmin} - {trainmax}\nTest Set: {testmin} - {testmax}")
In [65]:
mse2 = mse(test['Weekly_Sales'], predictions2)
rmse2 = rmse(test['Weekly_Sales'], predictions2)
print(f"MSE: {mse2}\nRMSE: {rmse2}")
SARIMAX - All Holidays¶
add the exog variable
In [66]:
byDatex = sales_all.groupby('Date').sum()
byDatex.IsHoliday = byDatex.IsHoliday.astype('bool').astype('int')
byDatex.head()
Out[66]:
In [67]:
#Split the data into train/test setsDatexs = byDatex.index
length = len(byDatex)
train_len = int(length*0.70)
train = byDatex.iloc[:train_len]
test = byDatex.iloc[train_len:]
In [68]:
model3 = SARIMAX(train['Weekly_Sales'],exog=train['IsHoliday'],order=(2,0,2),seasonal_order=(1,0,0,52),enforce_invertibility=False)
results = model3.fit()
results.summary()
Out[68]:
In [69]:
# Obtain predicted values
start=len(train)
end=len(train)+len(test)-1
exog_forecast = test[['IsHoliday']] # requires two brackets to yield a shape of (35,1)
predictions3 = results.predict(start=start, end=end, exog=exog_forecast)
models['AllHolidays'] = predictions3
models.head()
Out[69]:
In [70]:
models_lines.add_trace(
go.Scatter(
x=models.index,
y=models.AllHolidays,
name = "Model 3 - All Holidays"
))
models_lines.show()
In [71]:
from statsmodels.tools.eval_measures import mse,rmse
In [72]:
mse3 = mse(test['Weekly_Sales'], predictions3)
rmse3 = rmse(test['Weekly_Sales'], predictions3)
print(f"MODEL 1\n MSE: {mse1}\n RMSE: {rmse1}\n")
print(f"MODEL 2\n MSE: {mse2}\n RMSE: {rmse2}\n")
print(f"MODEL 3\n MSE: {mse3}\n RMSE: {rmse3}")
original data only include...incorporate all US consumer spending event (super bowl, black friday) and other commonly observed holdays that would drive consumer spending...assuming all us stores!!!! back to school...no exact date???
SOME HOLIDAYS HAVE TRAILING SALES (PREPARE)...SOME SAME DAY...
Forecast the Future¶
Retain on the all data on Model 1
In [148]:
byDate = byDate[['Weekly_Sales','IsHoliday']]
byDate.head()
Out[148]:
In [149]:
csv = pd.read_csv("data/fcast.csv")
fcast_dates = pd.DataFrame(csv)
fcast_dates.IsHoliday = fcast_dates.IsHoliday.astype('bool').astype('int')
fcast_dates.Date = pd.to_datetime(fcast_dates.Date)
fcast_dates.head()
Out[149]:
In [150]:
fcast_dates.set_index('Date', inplace=True)
fcast_dates.index.freq = 'W-FRI'
fcast_dates.index
Out[150]:
In [151]:
byDate.index
Out[151]:
In [152]:
len(fcast_dates)
len(test)
Out[152]:
In [153]:
model2x = SARIMAX(byDate['Weekly_Sales'],order=(2,0,2),seasonal_order=(1,0,0,52),enforce_invertibility=False)
results = model2x.fit()
exog_forecast = fcast_dates[['IsHoliday']]
fcast_predics = results.predict(fcast_dates.index.min(),fcast_dates.index.max(),exog=exog_forecast)
In [154]:
fcast_dates['Weekly_Sales'] = fcast_predics
fcast_dates['SalesType'] = "Forecast"
fcast_dates.head()
Out[154]:
In [155]:
byDate['SalesType'] = "Actual"
byDate.head()
Out[155]:
In [156]:
byDateFcast = pd.concat([byDate,fcast_dates])
byDateFcast
Out[156]:
In [157]:
fcast_line = px.line(byDateFcast, x=byDateFcast.index,y='Weekly_Sales', color='SalesType')
fcast_line.show()
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]: